<?php
	function f220($sql){
		//data handle instance
		//set request
		//define format
		//set format
		//define query string
		//process time
		//process fitler
		//create time handle instance
		$dataHandle = new DataHandle();
		$dataHandle->setRequest("220");

		$dataType = getValue($_GET['dataType']);
		$action = getValue($_GET['action']);

		if($dataType == "json"){
			$error = $dataHandle->processTime($startTime, $endTime);
			if($error == ""){
				$filter = $dataHandle->processFilters($startTime, $endTime, "lin_produced","start_time");
				$query = getQuery($startTime, $endTime, $filter);
				print($dataHandle->printJSON($sql, $query));
			}
			else
				print($error);
		}
		else if($action == "getYears"){
			$query = getYears();
			print($dataHandle->printJSON($sql, $query));
		}
		else if($action == "getLines"){
			$query = getLines();
			print($dataHandle->printJSON($sql, $query));
		}
		else{
			$temp = array();
			$temp['error'] = "220: Bad request";
			print(json_encode($temp));
		}

		unset($dataHandle);
	}
	function getQuery(&$startTime, &$endTime, &$filter){
		$queryString = 
			"
			select
			lin_produced.line_id as line,
			lin_produced.shift,
			lin_produced.speed,
			lin_produced.minutes as time,
			lin_produced.product_id as item,
			lin_products.description,
			lin_produced.pouches pouchesActual,
			lin_produced.pouches_total pouchesTotal,

			case
				when lin_produced.pouches_total=0
					then null
				else
					cast(
						round(
							(100*(lin_produced.pouches_total-lin_produced.pouches)/lin_produced.pouches_total)
						,1)
					AS numeric(8, 1)) 
			end as pouchesWaste,

			lin_produced.units as casesActual,
			lin_produced.units_total as casesTotal,

			case
				when lin_produced.units_total=0
					then null
				else 
					cast(
						round(
							(100*(lin_produced.pouches_total-lin_produced.pouches)/lin_produced.pouches_total)
						,1)
					AS numeric(8, 1)) 
			end as casesWaste,

			lin_produced.web as webActual,
			lin_produced.web_total as webTotal,

			case
				when lin_produced.web_total=0
					then null
				else 
					cast(
						round(
							(100*(lin_produced.pouches_total-lin_produced.pouches)/lin_produced.pouches_total)
						,1)
					AS numeric(8, 1)) 
			end as webWaste

			from lin_produced
			INNER JOIN lin_products ON lin_produced.product_id=lin_products.product_id";
			
		$queryString = $queryString.$filter;

		$queryString = $queryString."order by end_time desc;";

		return $queryString;
	}
	function getYears(){
		$queryString = 
			"SELECT DISTINCT 
				DATEPART(year, modified_Date) AS availableYear 
			FROM lin_Produced 
			ORDER BY availableYear desc";
		return $queryString;
	}
	function getLines(){
		$queryString = 
			"SELECT DISTINCT 
				line_id AS availableLine 
			FROM lin_Produced 
			ORDER BY availableLine asc";
		return $queryString;
	}
?>